import java.sql.* ;

class PurchaseOrder
{
	CreateConnection cc ;

	int purchase_id ;  // index-1 in both tables purchase_order and purchase_order_details
	int vendor_id ; // index-2 in purchase_order
	String order_number ; // index-3 in purchase_order

	java.sql.Date date ; // index-4 in purchase_order
	java.sql.Time time ; // index-5 in purchase_order

	float total_amount ; // index-6 in purchase_order


// As for one purchase_order - there will be a buying from one vendor with many products, so we will need to insert details
// of all products in purchase_order_details table for a purchase_id.

	String[] product_id ; // index-2 in purchase_order_details
	String[] quantity ; //index-3 in purchase_order_details

	String[] price ; //index-4 in purchase_order_details
	String[] VAT ;// index-5 in purchase_order_details








	PurchaseOrder()
	{

		try
		{

			cc = new CreateConnection() ;

		}
		catch(Exception e)
		{

		}


	}




PurchaseOrder(String[] s, String[] product_id, String[] quantity, String[] price, String[] VAT )
	{
		purchase_id = Integer.parseInt(s[0]) ;
		vendor_id = Integer.parseInt(s[1]) ;
		order_number = s[2] ;


		date = java.sql.Date.valueOf(s[3]) ;  // will convert "2012-09-12" type string into java.sql.Date object
		time = 	java.sql.Time.valueOf(s[4]) ;
		total_amount = Float.parseFloat(s[5]) ;
/*
		product_id = Integer.parseInt(s[5]) ;
		quantity = Integer.parseInt(s[6]) ;
		price = Float.parseFloat(s[7]) ;
		VAT = Float.parseFloat(s[8]) ;

*/

		this.product_id = product_id ;
		this.quantity = quantity;
		this.price = price ;
		this.VAT = VAT;

/*
		System.out.println(purchase_id);
		System.out.println(vendor_id);
		System.out.println(order_number);
		System.out.println(date);
		System.out.println(time);
		System.out.println(product_id);
		System.out.println(quantity);
		System.out.println(price);
		System.out.println(VAT);
*/


		try
		{

			cc = new CreateConnection() ;

		}
		catch(Exception e)
		{

		}


	}





	void insertDetailsInPurchaseOrder()
	{

		try
		{

			String sql1 = "Insert into purchase_order values(?,?,?,?,?,?)" ;

			PreparedStatement ps1 = cc.getStatement(sql1) ;

			ps1.setInt(1,purchase_id);
			ps1.setInt(2,vendor_id);
			ps1.setString(3,order_number);
			ps1.setDate(4,date);
			ps1.setTime(5,time);
			ps1.setFloat(6,total_amount) ;

			int x = ps1.executeUpdate() ;
			if(x>0)
			System.out.println("Record inserted in Purchase_order table");






		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());

			}

	}







void insertDetailsInPurchaseOrderDetails()
{

	try
	{

		int n = product_id.length ;
		while(n!=0)
		{

					String sql2 = "Insert into purchase_order_details values(?,?,?,?,?)" ;
					PreparedStatement ps2 = cc.getStatement(sql2) ;

								ps2.setInt(1,purchase_id);
								ps2.setInt(2,Integer.parseInt( product_id[n-1] ));
								ps2.setInt(3,Integer.parseInt( quantity[n-1] ));
								ps2.setFloat(4,Float.parseFloat( price[n-1] ));
								ps2.setFloat(5,Float.parseFloat( VAT[n-1] ));

								int y = ps2.executeUpdate() ;
								if(y>0)
					System.out.println("Record inserted in purchase_order_details table");



					n-- ;
	}

	}
	catch(Exception e)
			{
				System.out.println(e.getMessage());

			}


}


ResultSet getProductDetailsOfPurchaseId(int purchase_id)throws Exception
{


		String sql = "select * from purchase_order_details where purchase_id = ? " ;

		PreparedStatement ps = cc.getStatement(sql) ;

		ps.setInt(1,purchase_id) ;
		return ps.executeQuery() ;

}


ResultSet getPurchaseDetailsBetweenDates(String startDate, String endDate) throws Exception
{
	String sql = "select * from purchase_order where date between ? and ? " ;

	PreparedStatement ps = cc.getStatement(sql) ;

	ps.setString(1,startDate) ;
	ps.setString(2,endDate) ;

	return ps.executeQuery() ;


}


}